//
//  WJSignatureHistoryModel.swift
//  PDFSign
//
//  Created by 李永彬 on 2025/9/26.
//

import UIKit
import FMDB

class WJSignatureHistoryModel: WJDBProtocol {
    static var associatedDatabase: WJDatabase = .pdfInfo
    static var databaseConfig = WJDatabaseConfig(database: .pdfInfo)
    static var tableName: String { return "wj_sinature_history" }
    var id: Int = 0
    var imageData: Data?
    var name: String?
    var createdDate: Date = Date()
    // 0: 自己签名 1：使用字体
    var signType: Int = 0
    
    var isSelected: Bool = false 
    
    required init() {}
    
    required init?(resultSet: FMResultSet) {
        self.id = Int(resultSet.int(forColumn: "id"))
        self.imageData = resultSet.data(forColumn: "imageData")
        self.name = resultSet.string(forColumn: "name") ?? ""
        self.createdDate = resultSet.date(forColumn: "created_date") ?? Date()
        self.signType = Int(resultSet.int(forColumn: "sign_type"))
    }
    
    // 创建表的SQL
    static func createTableSQL() -> String {
        return """
        CREATE TABLE IF NOT EXISTS \(tableName) (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        imageData BLOB,
        name TEXT,
        created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
        sign_type INT
        )
        """
    }
    
    // 插入SQL
    @discardableResult
    static func insertSQL(for object: WJSignatureHistoryModel) -> (String, [Any]) {
        let sql = """
        INSERT INTO \(tableName) (imageData, name, created_date, sign_type)
        VALUES (?, ?, ?, ?)
        """
        var parameters: [Any] = []
        
        if let imageData = object.imageData {
            parameters.append(imageData)
        }
        
        if let name = object.name {
            parameters.append(name)
        }
        
        parameters.append(object.createdDate)
        
        parameters.append(object.signType)
        
        return (sql, parameters)
    }
    
    // 更新SQL
    static func updateSQL(for object: WJSignatureHistoryModel) -> (String, [Any]) {
        let sql = """
        UPDATE \(tableName)
        SET imageData = ?, name = ?, created_date = ?, sign_type = ?
        WHERE id = ?
        """

        var parameters: [Any] = []
        
        if let imageData = object.imageData {
            parameters.append(imageData)
        }
        
        if let name = object.name {
            parameters.append(name)
        }
        
        parameters.append(object.createdDate)
        
        parameters.append(object.signType)
        
        parameters.append(object.id)
        
        return (sql, parameters)
    }
    
    // 类型安全的查询方法
    static func findAllTyped() throws -> [WJSignatureHistoryModel] {
        let results = try findAll()
        return try convertToTypedResults(results)
    }
    
    static func findTyped(byId id: Int) throws -> WJSignatureHistoryModel? {
        let result = try find(byId: id)
        return try convertToTypedResult(result)
    }
    
    static func findTyped(where condition: String, parameters: [Any]?) throws -> [WJSignatureHistoryModel] {
        let results = try find(where: condition, parameters: parameters)
        return try convertToTypedResults(results)
    }
    
    // 自定义查询方法
    static func findByName(_ name: String) throws -> [WJSignatureHistoryModel] {
        let results = try find(where: "name LIKE ?", parameters: ["%\(name)%"])
        return try convertToTypedResults(results)
    }
    
    static func findRecentlyFiles() throws -> [WJSignatureHistoryModel] {
        let calendar = Calendar.current
        let oneYearAgo = calendar.date(byAdding: .year, value: -1, to: Date())!
        let results = try find(where: "created_date > ?", parameters: [oneYearAgo])
        return try convertToTypedResults(results)
    }
    
    static func findDrawSignature() throws -> [WJSignatureHistoryModel] {
        let results = try find(where: "sign_type = ?", parameters: [0])
        return try convertToTypedResults(results)
    }
    
    static func findFontSignature() throws -> [WJSignatureHistoryModel] {
        let results = try find(where: "sign_type = ?", parameters: [1])
        return try convertToTypedResults(results)
    }
}
